﻿using System;

using System.Collections.Generic;
using System.Text;
using System.Data.SQLite;
using System.IO;
using TimeTableTool.Properties;


namespace TimeTableTool
{
    class DBHelper
    {
        private static SQLiteConnection connection;
        private static readonly String databaseFile = "database.db";

        public static readonly String STATION_NAME = "name";
        public static readonly String STATION_ID = "id";

        public static SQLiteConnection GetConnection(String path)
        {
            bool emptyDatabase = false;

            //File.Delete(databaseFile);

            if (File.Exists(databaseFile) == false)
            {
                emptyDatabase = true;
            }

            if (connection == null || (connection.State != System.Data.ConnectionState.Open
                || connection.State != System.Data.ConnectionState.Connecting))
            {
                connection = new SQLiteConnection("Data Source=" + path + ";");
                connection.Open();

                //enable foreign key support
                SQLiteCommand pragmaFkCommand = new SQLiteCommand(connection);
                pragmaFkCommand.CommandText = "PRAGMA foreign_keys;";
                pragmaFkCommand.ExecuteNonQuery();

                //create table structure if necessary 
                SQLiteCommand databaseStructureCmnd = new SQLiteCommand(connection);
                databaseStructureCmnd.CommandText = Resources.sqlscript;
                databaseStructureCmnd.ExecuteNonQuery();

                /*
                //insert default data if necessary
                if (emptyDatabase)
                {
                    SQLiteCommand defaultDataCmnd = new SQLiteCommand(connection);
                    defaultDataCmnd.CommandText = Resources.default_data;
                    defaultDataCmnd.ExecuteNonQuery();
                }
                */

            }
            return connection;
        }

        ~DBHelper()
        {
            if (connection != null && connection.State != System.Data.ConnectionState.Closed)
            {
                connection.Close();
            }
        }
    }
}
